How to start a fulltext population from the command prompt

Applies to:

• Content Studio all versions running on SQL Server 2005 and later

As a part of Content Studio database maintenance it sometimes is necessary to repopulate the fulltext index. This is easy to do using a scheduled SQL Agent job. The Content Studio installation program also offers the possibility to install a daily job for incremental population combined with a weekly job that makes a full population of the index. From SQL Management Studio you can easily change the schedule of these jobs according to your needs.
Unfortunately, SQL Express Edition does not offer this possibility so as an alternative you can use the command line tool to accomplish the same thing.

• Log in to the database server, you can do this from a remote computer as well, but the computer must have SQL Server 2005 (or later) locally installed. Also, make sure that the your login account has the permission needed to repopulate the fulltext index in the database you want to maintain.
• Create a new directory on your computer, and name it to something useful, ex. SQL fulltext

• Create a new text file in this directory and name it incr-pop.cmd.
  Edit this file with Notepad and write the following lines of code (no extra line breaks are allowed).

  sqlcmd -S "INSTANCE_NAME" -E -d "DATABASE_NAME" -Q "SELECT 'Starting incremental population of database ' + DB_NAME() + ' ' + CAST(GETDATE() AS varchar(20));" >> repopulate.txt
  
  sqlcmd -S "INSTANCE_NAME" -E -d "DATABASE_NAME" -Q "ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] START INCREMENTAL POPULATION;" >> repopulate.txt
                          

  • Replace the string INSTANCE_NAME in the commands above with the real database instance name.
    For example: use LOCALHOST\SqlExpress if you have an Sql Express instance named SqlExpress running on your local computer. For default instances you can use the computer name or LOCALHOST if Sql Server runs on the current computer.
  • Replace the string DATABASE_NAME with the name of the Content Studio database to repopulate
• Start a new command window and navigate to the just created folder, using the cd command.
• Enter Incr-pop.cmd at the command prompt and press enter.
• sqlcmd outputs the command into the command window and the result of the operation into a new file, repopulate.txt

For each time you execute the command a new entry is added at the end of the repopulate.txt file, thus enabling you to keep records of the fulltext maintenance.

If you want to perform this task automatically according to a schedule you can set up a job in Windows Schedule Task manager. Remember to use a runtime account that has the needed privileges in Sql Server to perform the operation needed and that this account must have permission to write to the log file.

Note
The usaged of the cmd.exe program (located in the System32 directory) can be restricted to the user currently logged in (INTERACTIVE), Services and Administrators. By default this is true on Server 2003 but not on Vista/Server 2008 or XP.
If you use a dedicated, non local administrator account to run these jobs, make sure that this account has at least READ/EXECUTE permission on the cmd.exe file located in the System32 directory.

Starting with version 5.2 Content Studio now supports background fulltext index updating. This minimizes the need for the operations described in this article.